USE [Tfs_Warehouse]
GO

/****** Object:  StoredProcedure [dbo].[ReqandChilds]    Script Date: 10/08/2014 04:23:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE proc [dbo].[ReqandChilds] @ProjectNodeGUID nvarchar(255),@CollectionSK nvarchar(255),
@IterationSK varchar(255), @AreaSK varchar(255),@ReqWItype varchar(255)
as begin

Create TABLE #TempResultTable 
(
ID int, RequiremenID int,Title nvarchar(250),[State] nvarchar(100),WorkItemType nvarchar(100),CompletedWork float,RemainingWork float,IterationPath nvarchar(255),
AreaPath nvarchar(255) ,level int) 

Declare @Req_ID int
Declare @Child_ID int

Declare @Title nvarchar(250)
Declare @State nvarchar(100)
Declare @WorkItemType nvarchar(100)
Declare @RemainingWork float
Declare @CompletedWork float
Declare @OriginalEstimat float
Declare @IterationPath nvarchar(255)
Declare @AreaPath nvarchar(255)

Declare @Changset_ID int
Declare @ChangesetSK nvarchar(255)
Declare @CTitle nvarchar(250)
Declare @CState nvarchar(100)
Declare @CWorkItemType nvarchar(100)

Declare outerCursor CURSOR for
select reqID,Title,State,WorkItemType,CompletedWork,RemainingWork,IterationPath,AreaPath from (
SELECT  T1.System_Rev,T1.System_Id as reqID,T1.System_Title as Title,T1.System_State as 'State',T1.System_WorkItemType as WorkItemType,
tt.Microsoft_VSTS_Scheduling_CompletedWork as CompletedWork,tt.Microsoft_VSTS_Scheduling_RemainingWork as RemainingWork,
   ROW_NUMBER() OVER (partition by T1.System_Id ORDER BY T1.System_Rev Desc) AS RowNumber ,Iteration.IterationPath as IterationPath,Area.AreaPath as AreaPath,
   T1.IterationSK as IterationSK,T1.AreaSK as AreaSK
  FROM (SELECT DISTINCT * FROM [Tfs_Warehouse].[dbo].[DimWorkItem]) AS T1  inner join 
  [Tfs_Warehouse].[dbo].[FactWorkItemHistory] as tt on T1.WorkItemSK=tt.WorkItemSK inner join
  [Tfs_Warehouse].[dbo].[DimTeamProject] TP on T1.TeamProjectSK=TP.ProjectNodeSK inner join 
  [Tfs_Warehouse].[dbo].[DimIteration] Iteration on T1.IterationSK=Iteration.IterationSK
  inner join [Tfs_Warehouse].[dbo].[DimArea] Area on T1.AreaSK=Area.AreaSK
  where T1.System_WorkItemType=@ReqWItype and TP.ProjectNodeGUID=@ProjectNodeGUID and TP.ParentNodeSK=@CollectionSK

  ) Req where RowNumber=1 and (IterationSK=@IterationSK or AreaSK=@AreaSK)

Open outerCursor
Fetch next from outerCursor into @Req_ID,@Title,@State,@WorkItemType,@CompletedWork,@RemainingWork,@IterationPath,@AreaPath
 
While @@FETCH_STATUS = 0

Begin
Insert into #TempResultTable( ID , RequiremenID ,Title, [State] ,WorkItemType,RemainingWork,CompletedWork,IterationPath,AreaPath, level )values (@Req_ID,@Req_ID,@Title,@State,@WorkItemType,@CompletedWork,@RemainingWork,@IterationPath,@AreaPath,0)
	
	
	DECLARE innerCursor CURSOR FOR
			select ID,RequirementId,Title,State,WorkItemType,CompletedWork,RemainingWork,IterationPath,AreaPath from(
			Select wi.System_Id as ID,@Req_ID as RequirementId,wi.System_Title as Title,wi.System_State as State,wi.System_WorkItemType as WorkItemType,wi.System_ChangedDate,
			tt.Microsoft_VSTS_Scheduling_CompletedWork as CompletedWork,tt.Microsoft_VSTS_Scheduling_RemainingWork as RemainingWork,Iteration.IterationPath as IterationPath,Area.AreaPath as AreaPath,
			1 as level,ROW_NUMBER() OVER (partition by wi.System_Id ORDER BY wi.System_Rev DESC) AS RowNumber  from[Tfs_Warehouse].[dbo].[DimWorkItem] AS wi 
			  inner join [Tfs_Warehouse].[dbo].[FactWorkItemLinkHistory] as li 
			  on wi.System_Id=li.TargetWorkItemID inner join [Tfs_Warehouse].[dbo].[DimWorkItemLinkType] as b
			  on li.[WorkItemLinkTypeSK]=b.[WorkItemLinkTypeSK]   inner join 
  [Tfs_Warehouse].[dbo].[FactWorkItemHistory] as tt on wi.WorkItemSK=tt.WorkItemSK inner join 
  [Tfs_Warehouse].[dbo].[DimIteration] Iteration on wi.IterationSK=Iteration.IterationSK
  inner join [Tfs_Warehouse].[dbo].[DimArea] Area on wi.AreaSK=Area.AreaSK inner join
  [Tfs_Warehouse].[dbo].[DimTeamProject] TP on wi.TeamProjectSK=TP.ProjectNodeSK
			  where b.LinkName in ('Child','Tested By') and  li.SourceWorkItemID=@Req_ID and li.RemovedDate = CONVERT(DATETIME, '9999', 126) 
			  and TP.ProjectNodeGUID=@ProjectNodeGUID and li.TeamProjectCollectionSK=@CollectionSK
			   )emp  where RowNumber=1 order by ID 
  
			  Open innerCursor
			Fetch next from innerCursor into @Child_ID,@Req_ID,@Title,@State,@WorkItemType,@CompletedWork,@RemainingWork,@IterationPath,@AreaPath
 
			While @@FETCH_STATUS = 0
			Begin
			Insert into #TempResultTable( ID , RequiremenID ,Title, [State] ,WorkItemType,CompletedWork,RemainingWork,IterationPath,AreaPath, level )values (@Child_ID,@Req_ID,@Title,@State,@WorkItemType,@CompletedWork,@RemainingWork,@IterationPath,@AreaPath,1)
				print'Task or Test';	

	if(@WorkItemType<>'Test Case')
	begin
		DECLARE innerCursor2 CURSOR FOR
		select distinct ID, RequirementId,Title,ChangesetSK,State,WorkItemType from(
		Select dc.ChangesetID as ID,@Child_ID as RequirementId,dc.ChangesetTitle as Title,dc.ChangesetSK as ChangesetSK,'NULL' as State,'Changeset' as WorkItemType
		from[Tfs_Warehouse].[dbo].[DimWorkItem] wi inner join [Tfs_Warehouse].[dbo].[FactWorkItemChangeset] fc
		on wi.System_Id=fc.WorkItemID inner join [Tfs_Warehouse].[dbo].[DimChangeset] dc on fc.ChangesetSK=dc.ChangesetSK
		inner join   [Tfs_Warehouse].[dbo].[DimTeamProject] TP on wi.TeamProjectSK=TP.ProjectNodeSK
		where  wi.System_Id=@Child_ID and fc.RemovedDateTime = CONVERT(DATETIME, '9999', 126) and TP.ProjectNodeGUID=@ProjectNodeGUID and TP.ParentNodeSK=@CollectionSK
		)emp1   order by ID
			Open innerCursor2
			Fetch next from innerCursor2 into @Changset_ID,@Child_ID,@CTitle,@ChangesetSK,@CState,@CWorkItemType
		While @@FETCH_STATUS = 0
			Begin
			Insert into #TempResultTable( ID , RequiremenID ,Title, [State] ,WorkItemType,level )values (@Changset_ID,@Child_ID,@CTitle,@CState,@CWorkItemType,2)
				

			SELECT distinct wi.System_Id as ID, @Changset_ID as RequirementID,wi.System_Title as Title,wi.System_WorkItemType as WorkItemType
		into thirdtable FROM [Tfs_Warehouse].[dbo].[DimChangeset] dc inner join [Tfs_Warehouse].[dbo].[FactWorkItemChangeset] fc on dc.ChangesetSK=fc.ChangesetSK
		inner join [Tfs_Warehouse].[dbo].[DimWorkItem] wi on fc.WorkItemID=wi.System_Id inner join [Tfs_Warehouse].[dbo].[DimTeamProject] TP on wi.TeamProjectSK=TP.ProjectNodeSK
		where wi.System_WorkItemType='Code Review Request' and dc.ChangesetID=@Changset_ID and dc.ChangesetSK=@ChangesetSK and TP.ProjectNodeGUID=@ProjectNodeGUID 
		and TP.ParentNodeSK=@CollectionSK
			
			if(select count(*) from thirdtable)>0
			begin
			 Insert into #TempResultTable( ID , RequiremenID ,Title ,WorkItemType, level )
			 Select ID,RequirementId,Title,WorkItemType,3 from thirdtable
			
				
			 
			 end
			drop table thirdtable
		
			Fetch next from innerCursor2 into @Changset_ID,@Child_ID,@CTitle,@ChangesetSK,@CState,@CWorkItemType
			end
				
			Close innerCursor2
			DEALLOCATE innerCursor2
			
			end
				
	else
		begin
			select ID,RequirementId,Title,State,WorkItemType,CompletedWork,RemainingWork,IterationPath,AreaPath,level,RowNumber into ResultTable from(
			Select wi.System_Id as ID,@Child_ID as RequirementId,wi.System_Title as Title,wi.System_State as State,wi.System_WorkItemType as WorkItemType,wi.System_ChangedDate,
			tt.Microsoft_VSTS_Scheduling_CompletedWork as CompletedWork,tt.Microsoft_VSTS_Scheduling_RemainingWork as RemainingWork,
			Iteration.IterationPath as IterationPath,Area.AreaPath as AreaPath,2 as level,ROW_NUMBER() OVER (partition by wi.System_Id ORDER BY wi.System_Rev DESC) AS RowNumber 
			from[Tfs_Warehouse].[dbo].[DimWorkItem] AS wi inner join [Tfs_Warehouse].[dbo].[FactWorkItemLinkHistory] as li on wi.System_Id=li.TargetWorkItemID 
			inner join [Tfs_Warehouse].[dbo].[DimWorkItemLinkType] as b on li.[WorkItemLinkTypeSK]=b.[WorkItemLinkTypeSK] inner join [Tfs_Warehouse].[dbo].[FactWorkItemHistory] 
			as tt on wi.WorkItemSK=tt.WorkItemSK  inner join [Tfs_Warehouse].[dbo].[DimIteration] Iteration on wi.IterationSK=Iteration.IterationSK inner join 
			[Tfs_Warehouse].[dbo].[DimArea] Area on wi.AreaSK=Area.AreaSK where b.LinkName ='Tests' and  li.SourceWorkItemID=@Child_ID and 
			li.RemovedDate = CONVERT(DATETIME, '9999', 126) and li.TeamProjectCollectionSK=@CollectionSK and wi.System_WorkItemType='Bug')emp  where RowNumber=1 order by ID
			
			if(select count(*) from ResultTable)>0
			begin
			 Insert into #TempResultTable( ID , RequiremenID ,Title, [State] ,WorkItemType,CompletedWork,RemainingWork,IterationPath,AreaPath, level )
			 Select ID,RequirementId,Title,State,WorkItemType,CompletedWork,RemainingWork,IterationPath,AreaPath,level from ResultTable
			 
			 
			 end
			 drop table ResultTable
			 
				end
				
				Fetch next from innerCursor into @Child_ID,@Req_ID,@Title,@State,@WorkItemType,@CompletedWork,@RemainingWork,@IterationPath,@AreaPath
			

				end
Close innerCursor
DEALLOCATE innerCursor
Fetch next from outerCursor into @Req_ID,@Title,@State,@WorkItemType,@CompletedWork,@RemainingWork,@IterationPath,@AreaPath

End
Close outerCursor
DEALLOCATE outerCursor
select * from #TempResultTable
drop table #TempResultTable

end


GO


